/********************
* table 1 output for nsw
*******************/

%macro tableone (indata, hospital); 

* require distinct rows per ppn;
proc sql ;
	create table ppn_indata as
	select DISTINCT(ppn), max(Age) as Age, Sex, max(SA2_2011_CODE) as SA2_2011_CODE, 
		max(elix1) as elix1, 
		max(elix7) as elix7, 
		max(elix10) as elix10, 
		max(elix11) as elix11, 
		max(elix14) as elix14, 
		max(elix31) as elix31
	from &indata
	group by ppn;
quit;

* Age; 
title "Age at index episode"; 
proc means data = ppn_indata mean std; 
	var Age; 
run; 

* Gender; 
title "Sex"; 
proc freq data = ppn_indata; 
	table sex; 
run; 

* Neighbourhood income quintile; 
title "Neighbourhood (SA2) income quintile"; 

%sa2_conversion(cohort = ppn_indata, output = sa2_2016_cohort, level = ppn); 

proc sql; 
	create table temp2 as
	select a.*, b.quintile_personal
	from sa2_2016_cohort as a
	left join codes.income_sa2 as b
	on a.sa2_2016 = b.sa2; 
      quit;

proc tabulate data = temp2 out = quint_count; 
	class quintile_personal; 
	var n_levels; 
	table quintile_personal, n_levels * (sum PCTSUM);
run;

* Comorbid conditions; 
title "Comorbid conditions"; 
proc freq data = ppn_indata; 
	tables elix1
		elix31
		elix7
		elix11
		elix10
		elix14;
run; 

* Cancer diagnosis; 
title "Associated cancer diagnosis"; 
data &indata; 
	set &indata; 
	cancer = 0; 
	array diagnosis $ diagnosis_code1-diagnosis_code49 diagnosis_codeP;
	 do over diagnosis; 
		if diagnosis in:('C') then cancer = 1; 
	 end; 
run; 

proc sql ;
	create table ppn_cancer as
	select DISTINCT(ppn), max(cancer) as cancer
	from &indata
	group by ppn;
quit;

proc freq data = ppn_cancer; 
	tables cancer; 
run;

* Hospital characteristics; 
* from admissions-var hospital table; 

* update since some of these hospitals were excluded from cohort; 
data hospital_true; 
	set &indata; 
	keep facility_identifier_recode2; 
run; 

proc sort data = hospital_true 
	dupout = hospital_exclude nodupkey ;
	by facility_identifier_recode2 ;
run; 

proc sql; 
	create table hospital_count as
	select a.* 
	from &hospital as a
	inner join hospital_true as b
	on a.facility_identifier_recode2 = b.facility_identifier_recode2; 

title "Number of hospitals in NSW"; 
proc freq data = hospital_count nlevels; 
	table facility_identifier_recode2 / noprint; 
run; 

title "Annualised procedure volumes"; 
proc means data = &hospital mean std median q1 q3 ; 
	var annualised_n; 
run; 

title "Public hospital count";
proc sql; 
	create table public_count as
	select peer_group, count(distinct facility_identifier_recode2) as n_count
	from &indata
	group by peer_group; 

proc print data = public_count; 
run; 

		
%mend tableone; 

	
	
	
	
